﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CaDecList
{
    class Program
    {
        static void Main(string[] args)
        {
            DBHelper dbHelper = new DBHelper();

            string strSql = "select REC_NO,BIZ_DATE from DEC_OPER_LIST ";//" where REC_NO in "


            var dtTmp = dbHelper.GetDataTable(strSql, null);
            var noDateList = dbHelper.ToEntities<RecNoEnt>(dtTmp);


            foreach (var ite in noDateList)
            {
                SqlParameter[] parms = new SqlParameter[]
               {
                   new SqlParameter("@RecNo",ite.REC_NO),
                   new SqlParameter("@BizDate",ite.BIZ_DATE),
               };
                dbHelper.ExcluteProc("Proc_Exoprt_OPER_LIST", parms);
                Console.WriteLine(ite.REC_NO + "_" + ite.BIZ_DATE);
            }
            Console.Read();
        }

    }

    public class DBHelper
    {
        private const string _connString
            //= "Data Source=10.22.140.192;Initial Catalog=WHTSAFEWHTCLIENT_Test;User ID=sa;Password=test@1234";
            = "Data Source=127.0.0.1;Initial Catalog=WHTSAFEWHTCLIENT;User ID=dev;Password=dev";


        public DataTable GetDataTable(string strSql, SqlParameter[] parms)
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                if (parms != null)
                {
                    cmd.Parameters.AddRange(parms);
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtTmp = new DataTable();
                da.Fill(dtTmp);
                conn.Close();

                return dtTmp;
            }

        }

        public bool ExcluteSql(string strSql, SqlParameter[] parms)
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                if (parms != null)
                {
                    cmd.Parameters.AddRange(parms);
                }
                int res = cmd.ExecuteNonQuery();
                conn.Close();

                return res > 0;

            }
        }
        public bool ExcluteProc(string strProc, SqlParameter[] parms)
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(strProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (parms != null)
                {
                    cmd.Parameters.AddRange(parms);
                }
                int res = cmd.ExecuteNonQuery();
                conn.Close();

                return res > 0;

            }
        }
        /// <summary>
        /// DataTable to Entities
        /// </summary>
        /// <typeparam name="T">Entity</typeparam>
        /// <param name="dt">DataTable</param>
        /// <returns>ListT</returns>
        public List<T> ToEntities<T>(DataTable dt)/*必须来在于数据库来自于文件可能存在问题*/ where T : class,new()
        {
            //IsNullable
            if (null == dt || dt.Rows.Count == 0)
                return null;
            //Initial Entities
            List<T> entities = new List<T>();
            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    PropertyInfo[] pArray = typeof(T).GetProperties();
                    T entity = new T();
                    Array.ForEach<PropertyInfo>(pArray, p =>
                    {
                        if (dt.Columns.Contains(p.Name))
                        {
                            if (row[p.Name] != DBNull.Value)
                            {
                                p.SetValue(entity, row[p.Name], null);
                            }
                        }
                    });
                    entities.Add(entity);
                }
                return entities;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }



    public class RecNoEnt
    {
        public string REC_NO { get; set; }
        public DateTime BIZ_DATE { get; set; }
    }
}
